package com.cn.yds.bdy.dao;

import com.cn.yds.bdy.dao.model.Topic;
import com.cn.yds.bdy.dao.model.TopicModel;
import com.cn.yds.bdy.dao.model.TopicPo;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface TopicRepository extends JpaRepository<TopicModel,Integer> {

    List<TopicModel> findByTeacherId(Integer teacherId);

    @Query(value = "SELECT NEW com.cn.yds.bdy.dao.model.Topic(a.id,a.teacherId,a.topicName,a.description,b.name,d.value,b.tel) " +
            "FROM TopicModel a JOIN UserModel b ON b.id=a.teacherId AND b.role = 0" +
            "JOIN DictionaryModel d ON d.id =a.status and d.type=2 " +
            "WHERE a.status in ?1 " +
            "AND (a.topicName LIKE %?2% OR a.description LIKE %?2% OR b.name LIKE %?2% ) AND a.teacherId = ?3 ")
    Page<Topic> findByStatus(List<Integer> status, String searchText,Integer teacherId, Pageable pageable);


    @Query(value = "SELECT NEW com.cn.yds.bdy.dao.model.Topic(a.id,a.teacherId,a.topicName,a.description,b.name,d.value,b.tel) " +
            "FROM TopicModel a " +
            "JOIN UserModel b ON b.id=a.teacherId AND b.role = 0 " +
            "JOIN DictionaryModel d ON d.id =a.status AND d.type=2 " +
            "LEFT JOIN OPtModel c  ON c.topicId = a.id AND c.teacherOpinion = 0 " +
            "WHERE   a.status in ?1 " +
            "AND (a.topicName LIKE %?2% OR a.description LIKE %?2% OR b.name LIKE %?2% ) "
            +" GROUP BY a.id HAVING COUNT(a.id) <3")
    Page<Topic> findByStatusForStudent(List<Integer> status, String searchText, Pageable pageable);


    @Query(value = "select new com.cn.yds.bdy.dao.model.TopicPo(a.id,a.topicName,a.description,a.status, d.value) " +
            "from TopicModel a " +
            "join DictionaryModel d on a.type = d.id and d.type = 1 where a.id =:topicId")
    TopicPo findTopicPoById(@Param("topicId") Integer topicId);


}
